package br.com.lumis.saas.service.consumerclient.persistence.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import lumis.portal.PortalException;
import lumis.portal.UnexpectedException;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.util.ITransaction;
import br.com.lumis.saas.SaasRequestParameters;
import br.com.lumis.saas.service.consumerclient.model.Customer;

public class CustomerDAO {
	private static final String QUERY_GET_BY_ID ="select * from saas_customer where id = ?";
	private static final String QUERY_GET_BY_NAME ="select * from saas_customer where name = ?";
	private static final String QUERY_GET_BY_LUMUSERID ="select * from saas_customer where lumUserId = ?";
	private static final String QUERY_UPDATE_LUM_USERID ="update saas_customer set lumUserId = ? where name = ?";
	private static final EnvironmentDAO environmentDAO = new EnvironmentDAO();
	
	public Customer getById(String id, ITransaction transaction) throws PortalException{
		Customer customer = null;
		try
		{					
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement(QUERY_GET_BY_ID);
			try{
				statement.setString(1, id);
				ResultSet resultSet = statement.executeQuery();
				try{
					if(resultSet.next()){
						customer = new Customer();
						customer.setId(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_ID));
						customer.setName(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_NAME));												
						customer.setEnvironments(environmentDAO.getByCustomer(customer, transaction));
						customer.setSurname(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_SURNAME));
						return customer;
					}
				}
				finally{
					resultSet.close();
				}
			}
			finally{
				statement.close();
			}						
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
		
		return customer;
	}
		
	public Customer getByName(String name, ITransaction transaction) throws PortalException{
		Customer customer = null;
		try
		{					
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement(QUERY_GET_BY_NAME);
			try{
				statement.setString(1, name);
				ResultSet resultSet = statement.executeQuery();
				try{
					if(resultSet.next()){
						customer = new Customer();
						customer.setId(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_ID));
						customer.setName(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_NAME));												
						customer.setEnvironments(environmentDAO.getByCustomer(customer, transaction));
						customer.setSurname(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_SURNAME));
						return customer;
					}
				}
				finally{
					resultSet.close();
				}
			}
			finally{
				statement.close();
			}						
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
		
		return customer;
	}

	
	public void updateLumUserId(String name, String lumUserId, ITransaction transaction) throws PortalException{
		
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement(QUERY_UPDATE_LUM_USERID);
			try
			{
				statement.setString(1, lumUserId);
				statement.setString(2, name);
				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}

		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}

	}

	public Customer getByLumUserId(String lumUserId, ITransaction transaction) throws PortalException{
		Customer customer = null;
		try
		{					
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement(QUERY_GET_BY_LUMUSERID);
			try{
				statement.setString(1, lumUserId);
				ResultSet resultSet = statement.executeQuery();
				try{
					if(resultSet.next()){
						customer = new Customer();
						customer.setId(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_ID));
						customer.setName(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_NAME));												
						customer.setEnvironments(environmentDAO.getByCustomer(customer, transaction));
						
						return customer;
					}
				}
				finally{
					resultSet.close();
				}
			}
			finally{
				statement.close();
			}						
		}
		catch (PortalException e){
			throw e;
		}
		catch (Exception e){
			throw new UnexpectedException(e);
		}
		
		return customer;
	}
}
